--1 PROPERTIES
SELECT 
   TRIM(STATE_CD)||TRIM(A.PROPERTY_ID) TRAC_ID,
	A.PROPERTY_ID TRAC_NUM,
	TRIM(REGION) REGION, 
	CASE	WHEN TRIM(market_area) IN ('AUSTIN/SA','DALLAS/FO','HOUSTON/S','WEST TEXA') THEN 'TEXAS' 
	WHEN	 TRIM(market_area)  IN ('ORANGE CO','BAY AREA','FRESNO','LOS ANGEL','SACRAMENT','SAN DIEGO') THEN 'CALIFORNIA' 
	WHEN	 TRIM(market_area)  IN ('KANSAS CI') THEN 'KANSAS' 
	WHEN	 TRIM(market_area)  IN ('ST. LOUIS') THEN 'MISSOURI' 
	ELSE	TRIM(market_area) 
	END	STATE, 
	CASE SUBSTR(STATE,1,5)
	WHEN 'CONNE' THEN 'CT'
	WHEN 'TEXAS' THEN 'TX'
	WHEN 'GEORG' THEN 'GA'
	WHEN 'KANSA' THEN 'KS'
	WHEN 'MISSO' THEN 'MO'
	WHEN 'KENTU' THEN 'KY'
	WHEN 'MISSI' THEN 'MS'
	WHEN 'NORTH' THEN 'NC'
	WHEN 'NEVAD' THEN 'NV'
	WHEN 'SOUTH' THEN 'SC'
	WHEN 'TENNE' THEN 'TN'
	WHEN 'LOUIS' THEN 'LA'
	ELSE SUBSTR(STATE,1,2)
	END STATE_CD,
   TRIM(MARKET_AREA) MARKET,
   	CASE WHEN INDEX(AE_ASSIGNED,'(') >0 THEN SUBSTR(AE_ASSIGNED, 1, INDEX(AE_ASSIGNED,'(') -2) ELSE TRIM(AE_ASSIGNED) END AREA_MGR,
	CASE WHEN INDEX(AM,'(') >0 THEN SUBSTR(AM, 1, INDEX(AM,'(') -2) ELSE TRIM(AM)  END ACCT_MGR,
   TRIM(PORTFOLIO_NAME) PORTFOLIO_NM,
   TRIM(PROPERTY_NAME) PROPERTY_NM,
   	1 ADDRESSES, 
	1 GREENS, 
	1 SUBSCRIBERS, 
	1 HSIA, 
	1 IPTV, 
	1 VOIP, 
	1 HSIA_REVENUE, 
	1 IPTV_REVENUE, 
	1 VOIP_REVENUE, 
	1 OTHER_UVERSE_REVENUE, 
	1 TOTAL_REVENUE,
	100*GREENS /(ADDRESSES + 0.01) LIT,
	100*SUBSCRIBERS/(ADDRESSES + 0.01) INSERVICE,
	100*SUBSCRIBERS /(GREENS + 0.01)  SATURATION,
	100*IPTV_REVENUE /(TOTAL_REVENUE + 0.01) IPTVPercentage,
	100*HSIA_REVENUE /(TOTAL_REVENUE + 0.01) HSIAPercentage,
	100*VOIP_REVENUE /(TOTAL_REVENUE + 0.01)  VOIPPercentage,
	100*OTHER_UVERSE_REVENUE /(TOTAL_REVENUE + 0.01)  OtherPercentage,
	TOTAL_REVENUE /(SUBSCRIBERS + 0.01)  TotalPerSubscriber,
	IPTV_REVENUE /(IPTV + 0.01) IPTVPerSubscriber,
	HSIA_REVENUE /(HSIA + 0.01) HSIAPerSubscriber,
	VOIP_REVENUE /(VOIP + 0.01) VOIPPerSubscriber,
	OTHER_UVERSE_REVENUE /(SUBSCRIBERS + 0.01)  OtherPerSubscriber,
	COALESCE( SUBSTR(ADDRZIP,1,INDEX(ADDRZIP,'|')-1) ,'ADDRESS UNKNOWN') ADDRESS,
	COALESCE( SUBSTR(ADDRZIP,INDEX(ADDRZIP,'|')+1) ,'99999') ZIP
FROM
SMARTMOVES.TXSM101_SO_SCORE A
LEFT OUTER JOIN
(
SELECT PROPERTY_ID, CAST(MAX(ADDRESS||'|'|| ZIP)  AS VARCHAR(120)) ADDRZIP
FROM
SMARTMOVES.TXSM012_SMOVES_ADDRESSES
GROUP BY PROPERTY_ID
) X
ON A.PROPERTY_ID = X.PROPERTY_ID